﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using Labthink.PCSoft.UniversalHost4.Model;
using Labthink.PCSoft.UniversalHost4.Model.DataManage;
using Labthink.PCSoft.UniversalHost4.Model.Flow;
using Labthink.PCSoft.UniversalHost4.Model.Query;
using Labthink.PCSoft.UniversalHost4.Utility;
using NLog.Fluent;

namespace Labthink.PCSoft.UniversalHost4.DataAccess
{
    public class HistoryRepository : BaseRepository
    {
        private static string _publicPwd;

        private const string TempPath = "TempData";
        public static bool IsGmp { get; set; }

        #region public


        #region intput&output

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="disk"></param>
        /// <returns>0 成功 -1失败 -2容量不足 -3不存在，-4 U盘不存在</returns>
        public static int DataExport(DateTime start, DateTime end, string disk)
        {
            if (string.IsNullOrEmpty(disk)) disk = "harddisk2";
            if (!Directory.Exists(disk)) return -4;
            Log.Debug().Message("导出{0}-{1}数据文件 ", start, end).Write();
            var file = string.Format("{0}-{1}.data", start.ToString("yyyyMMdd"), end.ToString("yyyyMMdd"));
            if (!File.Exists(file))
            {
                var st = Convert.ToDateTime(start.ToString("yyyy-MM-dd") + " 00:00:00");
                var et = Convert.ToDateTime(end.ToString("yyyy-MM-dd") + " 23:59:59");
                var indexes = GetIndex(st, et, true);
                if (!indexes.Any())
                {
                    return -3;
                }

                var zipDir = Path.Combine(TempPath, "Zip");
                //在根目录下创建临时文件夹
                if (Directory.Exists(zipDir))
                {
                    Directory.Delete(zipDir, true);
                }
                Directory.CreateDirectory(zipDir);
                foreach (var index in indexes)
                {
                    if (File.Exists(index))
                    {
                        File.Move(index, Path.Combine(zipDir, Path.GetFileName(index)));
                    }
                }
                file = Path.Combine(zipDir, file);
                // var r = ZipTool.Zip(Directory.GetFiles(zipDir).ToList(), file, _publicPwd);
                var r = ZipTool.ZipDir(zipDir, file, _publicPwd);
                if (r <= 0) return -1;
            }

            var fi = new System.IO.FileInfo(file);
            if (fi.Length > GetDiskCapacity(disk))
            {
                return -2;
            }

            var tar = Path.Combine(disk, @"labthink\back");
            if (!Directory.Exists(tar))
            {
                Directory.CreateDirectory(tar);
            }
            try
            {
                var desc = Path.Combine(tar, Path.GetFileName(file));
                if (File.Exists(desc))
                {
                    File.Delete(desc);
                }
                File.Move(file, desc);
                if (Directory.Exists(TempPath))
                {
                    Directory.Delete(TempPath, true);
                }
                return 0;
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("导出{0}-{1}数据文件出错 ", start, end).Write();
                return -1;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="disk"></param>
        /// <param name="filePath"></param>
        /// <returns>0 成功 -1失败 -3不存在</returns>
        public static int DataExportToRemote(DateTime start, DateTime end, string disk, ref string filePath)
        {
            //if (string.IsNullOrEmpty(disk)) disk = "harddisk2";
            //if (!Directory.Exists(disk)) return -4;
            Log.Debug().Message("导出{0}-{1}数据文件 ", start, end).Write();
            var file = string.Format("{0}-{1}.data", start.ToString("yyyyMMdd"), end.ToString("yyyyMMdd"));
            if (!File.Exists(file))
            {
                var st = Convert.ToDateTime(start.ToString("yyyy-MM-dd") + " 00:00:00");
                var et = Convert.ToDateTime(end.ToString("yyyy-MM-dd") + " 23:59:59");
                var indexes = GetIndex(st, et, true);
                if (!indexes.Any())
                {
                    return -3;
                }

                var zipDir = Path.Combine(TempPath, "Zip");

                //在根目录下创建临时文件夹
                if (Directory.Exists(zipDir))
                {
                    Directory.Delete(zipDir, true);
                }

                Directory.CreateDirectory(zipDir);
                foreach (var index in indexes)
                {

                    if (File.Exists(index))
                    {
                        File.Move(index, Path.Combine(zipDir, Path.GetFileName(index)));
                    }
                }
                file = Path.Combine(zipDir, file);
                var r = ZipTool.ZipDir(zipDir, file, _publicPwd);
                if (r <= 0) return -1;
            }

            filePath = file;
            return 0;
        }

        /// <summary>
        /// 备份数据
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="disk"></param>
        /// <returns>0 成功 -1失败 -2容量不足 -3不存在，-4 U盘不存在</returns>
        public static DataManageRecord DataBack(DateTime start, DateTime end, string disk)
        {
            var dmr = new DataManageRecord();
            if (string.IsNullOrEmpty(disk)) disk = "harddisk2";
            if (!Directory.Exists(disk))
            {
                dmr.OperateResult = -4;
                return dmr;
            }
            dmr.Start = DateTime.Now;
            Log.Debug().Message("备份{0}-{1}数据文件 ", start, end).Write();
            var file = $"{start:yyyyMMdd}-{end:yyyyMMdd}.data";
            dmr.FileName = file;
            if (!File.Exists(file))
            {
                var st = Convert.ToDateTime(start.ToString("yyyy-MM-dd") + " 00:00:00");
                var et = Convert.ToDateTime(end.ToString("yyyy-MM-dd") + " 23:59:59");
                var indexes = GetIndex(st, et, false);
                if (!indexes.Any())
                {
                    dmr.OperateResult = -3;
                    return dmr;
                }

                var zipDir = Path.Combine(TempPath, "Zip");

                //在根目录下创建临时文件夹
                if (Directory.Exists(zipDir))
                {
                    Directory.Delete(zipDir, true);
                }

                Directory.CreateDirectory(zipDir);
                foreach (var index in indexes)
                {
                    if (File.Exists(index))
                    {
                        File.Copy(index, Path.Combine(zipDir, Path.GetFileName(index)), true);
                    }
                }

                //把数据库一起添加到压缩包
                File.Copy(DbFile, Path.Combine(zipDir, Path.GetFileName(DbFile)), true);

                file = Path.Combine(zipDir, file);
                var r = ZipTool.ZipDir(zipDir, file, _publicPwd);
                if (r <= 0)
                {
                    dmr.OperateResult = -1;
                    return dmr;
                }
            }

            var fi = new System.IO.FileInfo(file);
            dmr.FileSize = (fi.Length / (1024 * 1024.0)).Round(2).ToDouble();

            if (fi.Length > GetDiskCapacity(disk))
            {
                dmr.OperateResult = -2;
                return dmr;
            }

            var tar = Path.Combine(disk, @"labthink\back");
            if (!Directory.Exists(tar))
            {
                Directory.CreateDirectory(tar);
            }

            try
            {
                var desc = Path.Combine(tar, Path.GetFileName(file));
                if (File.Exists(desc))
                {
                    File.Delete(desc);
                }

                File.Move(file, desc);
                if (Directory.Exists(TempPath))
                {
                    Directory.Delete(TempPath, true);
                }
                dmr.OperateResult = 0;
                return dmr;
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("备份{0}-{1}数据文件出错 ",  start, end).Write();
                dmr.OperateResult = -1;
                return dmr;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="disk"></param>
        /// <param name="filePath"></param>
        /// <returns>0 成功 -1失败 -3不存在</returns>
        public static DataManageRecord DataBackToRemote(DateTime start, DateTime end, string disk, ref string filePath)
        {
            var dmr = new DataManageRecord();
            Log.Debug().Message("备份{0}-{1}数据文件 ", start, end).Write();
            var file = string.Format("{0}-{1}.data", start.ToString("yyyyMMdd"), end.ToString("yyyyMMdd"));
            dmr.FileName = file;
            dmr.Start = DateTime.Now;
            if (!File.Exists(file))
            {
                var st = Convert.ToDateTime(start.ToString("yyyy-MM-dd") + " 00:00:00");
                var et = Convert.ToDateTime(end.ToString("yyyy-MM-dd") + " 23:59:59");
                var indexes = GetIndex(st, et, false);
                if (!indexes.Any())
                {
                    dmr.OperateResult = -3;
                    return dmr;
                }

                var zipDir = Path.Combine(TempPath, "Zip");

                //在根目录下创建临时文件夹
                if (Directory.Exists(zipDir))
                {
                    Directory.Delete(zipDir, true);
                }

                Directory.CreateDirectory(zipDir);
                foreach (var index in indexes)
                {
                    if (File.Exists(index))
                    {
                        File.Copy(index, Path.Combine(zipDir, Path.GetFileName(index)), true);
                    }
                }

                //把数据库一起添加到压缩包
                File.Copy(DbFile, Path.Combine(zipDir, Path.GetFileName(DbFile)), true);

                file = Path.Combine(zipDir, file);
                var r = ZipTool.ZipDir(zipDir, file, _publicPwd);
                if (r <= 0)
                {
                    dmr.OperateResult = -1;
                    return dmr;
                }
            }

            var fi = new System.IO.FileInfo(file);
            dmr.FileSize = (fi.Length / (1024 * 1024.0)).Round(2).ToDouble();
            filePath = file;
            dmr.OperateResult = 0;
            return dmr;
        }

        /// <summary>
        /// 还原数据文件
        /// </summary>
        /// <param name="disk"></param>
        /// <returns>0成功 -1失败 -2没有找到要还原的文件</returns>
        public static DataManageRecord DataRestore(string disk)
        {
            var dmr = new DataManageRecord();
            if (string.IsNullOrEmpty(disk)) disk = "harddisk2";
            Log.Debug().Message("还原{0}的试验数据", disk).Write();
            var tar = Path.Combine(disk, @"labthink\restore");
            if (!Directory.Exists(tar))
            {
                Log.Debug().Message("没有找到对应的文件").Write();
                dmr.OperateResult = -2;
                return dmr;
            }

            var files = Directory.GetFiles(tar);
            if (files.Length == 0)
            {
                dmr.OperateResult = -2;
                return dmr;
            }
            dmr.Start = DateTime.Now;
            //在根目录下创建临时文件夹
            if (Directory.Exists(TempPath))
            {
                Directory.Delete(TempPath, true);
            }

            Directory.CreateDirectory(TempPath);
            var zips = new List<string>();
            foreach (var file in files)
            {
                var tf = Path.Combine(TempPath, Path.GetFileName(file));
                File.Move(file, tf);
                zips.Add(tf);
            }
            dmr.FileName = Path.GetFileName(files.First());
            var fi = new System.IO.FileInfo(files.First());
            dmr.FileSize = (fi.Length / (1024 * 1024.0)).Round(2).ToDouble();
            //解压并还原文件
            var sucess = -1;
            try
            {
                foreach (var file in zips)
                {
                    var r = ZipTool.UnZip(file, file.Replace(".", ""), _publicPwd);
                    if (!string.IsNullOrEmpty(r))
                    {
                        foreach (var dbfile in Directory.GetFiles(r))
                        {
                            if (!string.IsNullOrEmpty((Path.GetExtension(dbfile)))) continue;
                            var fn = Path.GetFileName(dbfile);
                            if (!fn.Contains("_")) continue;
                            RestoreOne(dbfile);
                            sucess++;
                        }
                    }
                    else
                    {
                        dmr.OperateResult = -1;
                        return dmr;
                    }
                }
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("还原试验试验数据失败").Write();
                dmr.OperateResult = -1;
                return dmr;
            }

            if (Directory.Exists(TempPath))
            {
                Directory.Delete(TempPath, true);
            }

            dmr.OperateResult = sucess;
            return dmr;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static int DataRestoreFromRemote(string filePath, out string nf)
        {
            var dmr = new DataManageRecord();
            var zips = new List<string> { filePath };
            dmr.FileName = Path.GetFileName(filePath);
            var fi = new System.IO.FileInfo(filePath);
            dmr.FileSize = (fi.Length / (1024 * 1024.0)).Round(2).ToDouble();
            dmr.Start = DateTime.Now;
            nf = string.Empty;
            //解压并还原文件
            var sucess = -1;
            try
            {
                foreach (var file in zips)
                {
                    var r = ZipTool.UnZip(file, file.Replace(".", ""), _publicPwd);
                    if (!string.IsNullOrEmpty(r))
                    {

                        foreach (var dbfile in Directory.GetFiles(r))
                        {
                            if (!string.IsNullOrEmpty((Path.GetExtension(dbfile)))) continue;
                            var fn = Path.GetFileName(dbfile);
                            if (!fn.Contains("_")) continue;
                            RestoreOne(dbfile);
                            sucess++;
                        }
                    }
                    else
                    {
                        return -1;
                    }
                }
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("还原试验试验数据失败").Write();
                return -1;
            }

            if (Directory.Exists(TempPath))
            {
                Directory.Delete(TempPath, true);
            }

            return sucess;
        }

        /// <summary>
        /// 获取所有Upan
        /// 查找根目录下所有Harddisk开头的盘符
        /// </summary>
        /// <returns></returns>
        public static List<string> GetUsbDisks()
        {
            Log.Debug().Message("读取u盘信息").Write();
            var disks = Directory.GetDirectories(@"\");
            var usb = new List<string>();
            foreach (var disk in disks)
            {
                if (disk.ToLower().StartsWith("\\harddisk") && !string.Equals(disk.ToLower(), "\\harddisk"))
                {
                    usb.Add(disk);
                }
            }

            return usb;
        }

        /// <summary>
        /// 备份数据库
        /// </summary>
        /// <returns>0成功 -1失败，-2U盘空间不足，-4 没有U盘</returns>
        public static int DbBackup()
        {
            var disks = GetUsbDisks();
            if (disks == null || !disks.Any()) return -4;
            Log.Debug().Message("备份数据库").Write();
            IsDbExist();
            var tar = DateTime.Now.ToString("yyyyMMddHHmm") + ".bak";
            //File.Copy(DbFile, "App.db", true);
            var r = ZipTool.Zip(DbFile, tar, _publicPwd);
            if (r < 0)
            {
                Log.Error("备份数据库失败").Write();
                return -1;
            }

            var fi = new System.IO.FileInfo(tar);
            if (fi.Length > GetDiskCapacity(disks.First()))
            {
                return -2;
            }

            var dir = Path.Combine(disks.First(), @"labthink\db");
            if (!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }
            try
            {
                var file = Path.Combine(dir, tar);
                File.Move(tar, file);
                if (Directory.Exists(TempPath))
                {
                    Directory.Delete(TempPath, true);
                }

                return 0;
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("备份数据库失败").Write();
                return -1;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static int DbBackupToRemote(ref string filePath)
        {
            IsDbExist();
            var tar = DateTime.Now.ToString("yyyyMMddHHmm") + ".bak";
            //File.Copy(DbFile, "App.db", true);
            var r = ZipTool.Zip(DbFile, tar, _publicPwd);
            if (r < 0)
            {
                Log.Error("备份数据库失败").Write();
                filePath = "";
                return -1;
            }

            filePath = tar;
            return 0;
        }

        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <returns>0成功 -1失败，-2没有数据</returns>
        public static int DbRestore()
        {
            var disks = GetUsbDisks();
            if (disks == null || !disks.Any()) return -2;
            Log.Debug().Message("还原数据库").Write();
            var tar = Path.Combine(disks.First(), @"labthink\db");
            if (!Directory.Exists(tar))
            {
                Log.Debug().Message("没有找到对应的文件").Write();
                return -2;
            }

            var files = Directory.GetFiles(tar);
            if (files.Length == 0) return -2;

            //在根目录下创建临时文件夹
            if (Directory.Exists(TempPath))
            {
                Directory.Delete(TempPath, true);
            }
            Directory.CreateDirectory(TempPath);
            var zips = new List<string>();
            foreach (var file in files)
            {
                var tf = Path.Combine(TempPath, Path.GetFileName(file));
                File.Move(file, tf);
                zips.Add(tf);
            }

            //解压并还原文件
            var sucess = -1;
            try
            {
                foreach (var file in zips)
                {
                    var r = ZipTool.UnZip(file, file.Replace(".", ""), _publicPwd);
                    if (!string.IsNullOrEmpty(r))
                    {

                        foreach (var dbfile in Directory.GetFiles(r))
                        {
                            if (Path.GetFileName(dbfile).ToUpper() != "APP.DB") continue;
                            File.Copy(dbfile, DbFile, true);
                            sucess++;
                        }
                    }
                    else
                    {
                        return -1;
                    }
                }
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Write();
                return -1;
            }


            if (Directory.Exists(TempPath))
            {
                Directory.Delete(TempPath, true);
            }

            return sucess;
        }

        public static int DbRestoreFromRemote(string filePath, out string nf)
        {
            nf = string.Empty;
            var zips = new List<string> { filePath };

            //解压并还原文件
            var sucess = -1;
            try
            {
                foreach (var file in zips)
                {
                    var r = ZipTool.UnZip(file, file.Replace(".", ""), _publicPwd);
                    if (!string.IsNullOrEmpty(r))
                    {
                        foreach (var dbfile in Directory.GetFiles(r))
                        {
                            if (Path.GetFileName(dbfile).ToUpper() != "APP.DB") continue;
                            File.Copy(dbfile, DbFile, true);
                            sucess++;
                        }
                    }
                    else
                    {
                        return -1;
                    }
                }

            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Write();
                return -1;
            }

            if (Directory.Exists(TempPath))
            {
                Directory.Delete(TempPath, true);
            }

            return sucess;
        }

        /// <summary>
        /// 导出试验数据
        /// </summary>
        /// <returns></returns>
        public static int ExportTestData(List<string> files, string disk, bool isEncrypt)
        {
            var tarDir = Path.Combine(disk, @"labthink\testdata");
            if (!Directory.Exists(tarDir))
            {
                Directory.CreateDirectory(tarDir);
            }
            try
            {
                var fc = 0;
                foreach (var file in files)
                {
                    if (!File.Exists(file)) continue;
                    if (isEncrypt)
                    {
                        var fn = Path.GetFileName(file);
                        var r = ZipTool.Zip(file, fn, _publicPwd);
                        if (r > 0)
                        {
                            var tarFile = Path.Combine(tarDir, fn);
                            if (File.Exists(tarFile)) File.Delete(tarFile);
                            File.Move(fn, tarFile);
                            fc++;
                        }
                    }
                    else
                    {
                        File.Copy(file, Path.Combine(tarDir, Path.GetFileName(file)), true);
                        fc++;
                    }
                }

                return fc;
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("导出试验数据出错").Write();
                return -1;
            }
        }

        /// <summary>
        /// 压缩文件
        /// </summary>
        /// <param name="files"></param>
        /// <param name="isEncrypt"></param>
        /// <param name="targetFile"></param>
        /// <returns></returns>
        public static string ZipTestData(List<string> files, bool isEncrypt, string targetFile)
        {
            try
            {
                targetFile = targetFile.Replace(".", "").Replace(":", "");
                var tmp = Path.Combine(TempPath, targetFile);
                Directory.CreateDirectory(tmp);
                foreach (var file in files)
                {
                    File.Copy(file, Path.Combine(tmp, Path.GetFileName(file)));
                }

                var fn = targetFile + ".data";
                var r = ZipTool.ZipDir(tmp, fn, isEncrypt ? _publicPwd : "");
                if (r > 0) return fn;
                return "";
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("压缩文件出错").Write();
                return "";
            }
        }

        #endregion

        #region query

        public static PagedList<TestIndex> GetIndex(QueryData query)
        {
            if (query.PageCount > 0) return GetIndexByTestId(query);
            return GetIndexByStartTime(query);
        }

        private static PagedList<TestIndex> GetIndexByStartTime(QueryData query)
        {
            IsDbExist();
            Log.Debug().Message("查询第{0}页{1}条试验记录", query.PageIndex, query.PageCount).Write();
            if (query.PageIndex == 0) query.PageIndex = 1;
            var logs = new List<TestIndex>();
            if (query.PageIndex <= 0)
            {
                return null;
            }

            var objs = new List<object>();
            var sqlData = new StringBuilder("SELECT * FROM TestIndex WHERE Flag=0");
            var sqlCount = new StringBuilder("SELECT COUNT(1) FROM TestIndex WHERE Flag=0");
            if (!string.IsNullOrEmpty(query.TestName))
            {
                sqlData.Append(" AND TestName LIKE @TestName");
                sqlCount.Append(" AND TestName LIKE @TestName");
                objs.Add("%" + query.TestName + "%");
            }

            if (!string.IsNullOrEmpty(query.SampleName))
            {
                sqlData.Append(" AND SampleName LIKE @SampleName ");
                sqlCount.Append(" AND SampleName LIKE @SampleName");
                objs.Add("%" + query.SampleName + "%");
            }

            if (!string.IsNullOrEmpty(query.SampleMaterial))
            {
                sqlData.Append(" AND SampleMaterial LIKE @SampleMaterial");
                sqlCount.Append(" AND SampleMaterial LIKE @SampleMaterial");
                objs.Add("%" + query.SampleMaterial + "%");
            }

            if (!string.IsNullOrEmpty(query.SampleNo))
            {
                sqlData.Append(" AND SampleNo LIKE @SampleNo");
                sqlCount.Append(" AND SampleNo LIKE @SampleNo");
                objs.Add("%" + query.SampleNo + "%");
            }

            if (!string.IsNullOrEmpty(query.SampleBatch))
            {
                sqlData.Append(" AND SampleBatch LIKE @SampleBatch");
                sqlCount.Append(" AND SampleBatch LIKE @SampleBatch");
                objs.Add("%" + query.SampleBatch + "%");
            }

            if (!string.IsNullOrEmpty(query.OpName))
            {
                sqlData.Append(" AND OpName LIKE @OpName");
                sqlCount.Append(" AND OpName LIKE @OpName");
                objs.Add("%" + query.OpName + "%");
            }

            if (!string.IsNullOrEmpty(query.Tag))
            {
                sqlData.Append(" AND Tag LIKE @Tag");
                sqlCount.Append(" AND Tag LIKE @Tag");
                objs.Add("%" + query.Tag + "%");
            }

            if (!string.IsNullOrEmpty(query.Remark))
            {
                sqlData.Append(" AND Remark LIKE @Remark");
                sqlCount.Append(" AND Remark LIKE @Remark");
                objs.Add("%" + query.Remark + "%");
            }

            if (query.TestType != null && query.TestType.Value > 0)
            {
                sqlData.Append(" AND TestType=@TestType");
                sqlCount.Append(" AND TestType=@TestType");
                objs.Add(query.TestType.Value);
            }

            if (query.Sign != null && query.Sign.Value >= 0)
            {
                sqlData.Append(" AND ESignal=@ESignal");
                sqlCount.Append(" AND ESignal=@ESignal");
                objs.Add(query.Sign.Value);
            }

            //if (query.CellOrCup != null && query.CellOrCup > 0)
            //{
            //    sqlData.Append(" AND CupNo=@CupNo");
            //    sqlCount.Append(" AND CupNo=@CupNo");
            //    objs.Add(query.CellOrCup.Value);
            //}

            if (query.SelfQuery != null && query.SelfQuery.Count > 0)
            {
                foreach (var fd in query.SelfQuery)
                {
                    if (fd.Value.ToInt32() > 0)
                    {
                        sqlData.Append(" AND ").Append(fd.ColumnName).Append("=@").Append(fd.ColumnName);
                        sqlCount.Append(" AND ").Append(fd.ColumnName).Append("=@").Append(fd.ColumnName);
                        objs.Add(fd.Value);
                    }

                }
            }

            if (query.Start.HasValue)
            {
                sqlData.Append(" AND StartTime>=@StartTime ");
                sqlCount.Append(" AND StartTime>=@StartTime ");
                objs.Add(DateTime.Parse(query.Start.Value.ToString("yyyy-MM-dd") + " 00:00:00"));
            }

            if (query.End.HasValue)
            {
                sqlData.Append("  AND EndTime <=@EndTime");
                sqlCount.Append(" AND EndTime <=@EndTime");
                objs.Add(DateTime.Parse(query.End.Value.ToString("yyyy-MM-dd") + " 23:59:59"));
            }


            var strCount = string.Format("SELECT COUNT(1) FROM ({0} GROUP BY StartTime ) ", sqlData);
            var strData = string.Format("SELECT * FROM ({0}) WHERE StartTime=(SELECT StartTime FROM ({0}) GROUP BY StartTime ORDER BY StartTime DESC LIMIT {1},1)", sqlData, query.PageIndex - 1);

            int count;
            var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
            con.Open();
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    var reader = SqliteHelper.ExecuteReader(cmd, strData, objs.ToArray());
                    while (reader.Read())
                    {
                        var ti = new TestIndex
                        {
                            Id = reader["Id"].ToInt32(),
                            TestName = reader["TestName"].ToString(),
                            SampleName = reader["SampleName"].ToString(),
                            SampleMaterial = reader["SampleMaterial"].ToString(),
                            SampleNo = reader["SampleNO"].ToString(),
                            SampleBatch = reader["SampleBatch"].ToString(),
                            TestType = reader["TestType"] == null ? 0 : reader["TestType"].ToInt32(),
                            OpName = reader["OPName"].ToString(),
                            FilePath = reader["FilePath"].ToString(),
                            FileName = reader["FileName"].ToString(),
                            SysTime = reader["SysTime"].ToDateTime(),
                            StartTime = reader["StartTime"].ToDateTime(),
                            EndTime = reader["EndTime"].ToDateTime(),
                            TestId = reader["TestId"].ToInt32(),
                            ESignal = reader["ESignal"] == null ? 0 : reader["ESignal"].ToInt32(),
                            Tag = reader["Tag"].ToString(),
                            Remark = reader["Remark"].ToString()
                        };

                        if (query.SelfQuery != null && query.SelfQuery.Count > 0)
                        {
                            foreach (var sq in query.SelfQuery)
                            {
                                sq.Value = reader[sq.ColumnName];
                            }
                            ti.SelfQuery = query.SelfQuery.Clone();
                        }

                        logs.Add(ti);
                    }

                    reader.Close();
                }
                catch (Exception e)
                {
                    Log.Error().Exception(e).Message("查询实验记录出错:{0}", strData).Write();
                    if (con.State != ConnectionState.Closed) con.Close();
                    return null;
                }
            }

            if (con.State != ConnectionState.Open) con.Open();
            try
            {
                count = SqliteHelper.ExecuteScalar(DbFile, sqlCount.ToString(), objs.ToArray()).ToInt32();
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("获取试验记录总数出错:{0}",  sqlCount.ToString()).Write();
                if (con.State != ConnectionState.Closed) con.Close();
                return null;
            }

            var page = 0;
            if (con.State != ConnectionState.Open) con.Open();
            try
            {
                page = SqliteHelper.ExecuteScalar(DbFile, strCount, objs.ToArray()).ToInt32();
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("获取页数出错:{0}",  strCount).Write();
                if (con.State != ConnectionState.Closed) con.Close();
                return null;
            }

            if (con.State != ConnectionState.Closed) con.Close();

            return new PagedList<TestIndex>(logs)
            {
                TotalCount = count,
                PageIndex = query.PageIndex,
                PageSize = logs.Count,
                PageCount = page
            };
        }
        /// <summary>
        /// 获取试验索引
        /// </summary>
        /// <returns></returns>
        private static PagedList<TestIndex> GetIndexByTestId(QueryData query)
        {
            IsDbExist();
            Log.Debug().Message("查询第{0}页{1}条试验记录", query.PageIndex, query.PageCount).Write();
            if (query.PageIndex == 0) query.PageIndex = 1;
            var logs = new List<TestIndex>();
            if (query.PageIndex <= 0 || query.PageCount <= 0)
            {
                return null;
            }

            var objs = new List<object>();
            var sqlData = new StringBuilder("SELECT * FROM TestIndex WHERE Flag=0");
            var sqlCount = new StringBuilder("SELECT COUNT(1) FROM TestIndex WHERE Flag=0");

            if (!string.IsNullOrEmpty(query.TestName))
            {
                sqlData.Append(" AND TestName LIKE @TestName");
                sqlCount.Append(" AND TestName LIKE @TestName");
                objs.Add("%" + query.TestName + "%");
            }

            if (!string.IsNullOrEmpty(query.SampleName))
            {
                sqlData.Append(" AND SampleName LIKE @SampleName ");
                sqlCount.Append(" AND SampleName LIKE @SampleName");
                objs.Add("%" + query.SampleName + "%");
            }

            if (!string.IsNullOrEmpty(query.SampleMaterial))
            {
                sqlData.Append(" AND SampleMaterial LIKE @SampleMaterial");
                sqlCount.Append(" AND SampleMaterial LIKE @SampleMaterial");
                objs.Add("%" + query.SampleMaterial + "%");
            }

            if (!string.IsNullOrEmpty(query.SampleNo))
            {
                sqlData.Append(" AND SampleNo LIKE @SampleNo");
                sqlCount.Append(" AND SampleNo LIKE @SampleNo");
                objs.Add("%" + query.SampleNo + "%");
            }

            if (!string.IsNullOrEmpty(query.SampleBatch))
            {
                sqlData.Append(" AND SampleBatch LIKE @SampleBatch");
                sqlCount.Append(" AND SampleBatch LIKE @SampleBatch");
                objs.Add("%" + query.SampleBatch + "%");
            }

            if (!string.IsNullOrEmpty(query.OpName))
            {
                sqlData.Append(" AND OpName LIKE @OpName");
                sqlCount.Append(" AND OpName LIKE @OpName");
                objs.Add("%" + query.OpName + "%");
            }

            if (!string.IsNullOrEmpty(query.Tag))
            {
                sqlData.Append(" AND Tag LIKE @Tag");
                sqlCount.Append(" AND Tag LIKE @Tag");
                objs.Add("%" + query.Tag + "%");
            }

            if (!string.IsNullOrEmpty(query.Remark))
            {
                sqlData.Append(" AND Remark LIKE @Remark");
                sqlCount.Append(" AND Remark LIKE @Remark");
                objs.Add("%" + query.Remark + "%");
            }

            if (query.TestType != null && query.TestType.Value > 0)
            {
                sqlData.Append(" AND TestType=@TestType");
                sqlCount.Append(" AND TestType=@TestType");
                objs.Add(query.TestType.Value);
            }

            if (query.Sign != null && query.Sign.Value >= 0)
            {
                sqlData.Append(" AND ESignal=@ESignal");
                sqlCount.Append(" AND ESignal=@ESignal");
                objs.Add(query.Sign.Value);
            }

            //if (query.CellOrCup != null && query.CellOrCup > 0)
            //{
            //    sqlData.Append(" AND CupNo=@CupNo");
            //    sqlCount.Append(" AND CupNo=@CupNo");
            //    objs.Add(query.CellOrCup.Value);
            //}

            if (query.SelfQuery != null && query.SelfQuery.Count > 0)
            {
                foreach (var fd in query.SelfQuery)
                {
                    if (fd.Value != null && fd.Value + "" != "-1")
                    {
                        sqlData.Append(" AND ").Append(fd.ColumnName).Append("=@").Append(fd.ColumnName);
                        sqlCount.Append(" AND ").Append(fd.ColumnName).Append("=@").Append(fd.ColumnName);
                        objs.Add(fd.Value);
                    }

                }
            }

            if (query.Start.HasValue)
            {
                sqlData.Append(" AND StartTime>=@StartTime ");
                sqlCount.Append(" AND StartTime>=@StartTime ");
                objs.Add(DateTime.Parse(query.Start.Value.ToString("yyyy-MM-dd") + " 00:00:00"));
            }

            if (query.End.HasValue)
            {
                sqlData.Append("  AND EndTime <=@EndTime");
                sqlCount.Append(" AND EndTime <=@EndTime");
                objs.Add(DateTime.Parse(query.End.Value.ToString("yyyy-MM-dd") + " 23:59:59"));
            }

            sqlData.Append(" ORDER BY TestId DESC LIMIT " + (query.PageIndex - 1) * query.PageCount + "," +
                query.PageCount);
            int count;
            var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
            con.Open();
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    var reader = SqliteHelper.ExecuteReader(cmd, sqlData.ToString(), objs.ToArray());
                    while (reader.Read())
                    {
                        var ti = new TestIndex
                        {
                            Id = reader["Id"].ToInt32(),
                            TestName = reader["TestName"].ToString(),
                            SampleName = reader["SampleName"].ToString(),
                            SampleMaterial = reader["SampleMaterial"].ToString(),
                            SampleNo = reader["SampleNO"].ToString(),
                            SampleBatch = reader["SampleBatch"].ToString(),
                            TestType = reader["TestType"] == null ? 0 : reader["TestType"].ToInt32(),
                            OpName = reader["OPName"].ToString(),
                            FilePath = reader["FilePath"].ToString(),
                            FileName = reader["FileName"].ToString(),
                            SysTime = reader["SysTime"].ToDateTime(),
                            StartTime = reader["StartTime"].ToDateTime(),
                            EndTime = reader["EndTime"].ToDateTime(),
                            TestId = reader["TestId"].ToInt32(),
                            ESignal = reader["ESignal"] == null ? 0 : reader["ESignal"].ToInt32(),
                            Tag = reader["Tag"].ToString(),
                            Remark = reader["Remark"].ToString()
                        };

                        if (query.SelfQuery != null && query.SelfQuery.Count > 0)
                        {
                            foreach (var sq in query.SelfQuery)
                            {
                                sq.Value = reader[sq.ColumnName];
                            }
                            ti.SelfQuery = query.SelfQuery.Clone();
                        }

                        logs.Add(ti);
                    }

                    reader.Close();
                }
                catch (Exception e)
                {
                    Log.Error().Exception(e).Message("查询实验记录出错:{0}",  sqlData.ToString()).Write();
                    if (con.State != ConnectionState.Closed) con.Close();
                    return null;
                }
            }

            if (con.State != ConnectionState.Open) con.Open();
            try
            {
                count = SqliteHelper.ExecuteScalar(DbFile, sqlCount.ToString(), objs.ToArray()).ToInt32();
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("获取试验记录总数出错:{0}",  sqlCount.ToString()).Write();
                if (con.State != ConnectionState.Closed) con.Close();
                return null;
            }

            if (con.State != ConnectionState.Closed) con.Close();

            return new PagedList<TestIndex>(logs)
            {
                TotalCount = count,
                PageIndex = query.PageIndex,
                PageSize = logs.Count,
                PageCount = (int)Math.Ceiling((double)count / query.PageCount)
            };
        }

        /// <summary>
        /// 根据id获取试验文件路径
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public static List<string> GetFilesById(List<int> ids)
        {
            IsDbExist();
            Log.Debug().Message("根据Id获取试验文件路径").Write();
            var sqlWhere = new StringBuilder("");
            ids.ForEach(id => sqlWhere.Append(id).Append(","));
            var sql = string.Format("SELECT FilePath FROM TestIndex WHERE Id IN ({0})",
                sqlWhere.Remove(sqlWhere.Length - 1, 1));
            var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
            con.Open();
            var files = new List<string>();
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    var reader = SqliteHelper.ExecuteReader(cmd, sql, null);
                    while (reader.Read())
                    {
                        files.Add(reader[0].ToString());
                    }
                    reader.Close();
                }
                catch (Exception e)
                {
                    Log.Error().Exception(e).Message("根据Id获取试验文件路径出错：{0}",  sql).Write();
                }
            }

            return files;
        }

        /// <summary>
        /// 根据试验编号获取试验文件路径
        /// </summary>
        /// <param name="testIds"></param>
        /// <returns></returns>
        public static List<string> GetFilesByTestId(List<int> testIds)
        {
            IsDbExist();
            Log.Debug().Message("根据试验编号获取试验文件路径").Write();
            var sqlWhere = new StringBuilder("");
            testIds.ForEach(id => sqlWhere.Append(id).Append(","));
            var sql = string.Format("SELECT FilePath FROM TestIndex WHERE TestId IN ({0})",
                sqlWhere.Remove(sqlWhere.Length - 1, 1));
            var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
            con.Open();
            var files = new List<string>();
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    var reader = SqliteHelper.ExecuteReader(cmd, sql, null);
                    while (reader.Read())
                    {
                        files.Add(reader[0].ToString());
                    }
                    reader.Close();
                }
                catch (Exception e)
                {
                    Log.Error().Exception(e).Message("根据试验编号获取试验文件路径出错：{0}",  sql).Write();
                }
            }

            return files;
        }

        /// <summary>
        /// 根据试验文件名称获取试验文件路径
        /// </summary>
        /// <param name="names"></param>
        /// <returns></returns>
        public static List<string> GetFilesByTestName(List<string> names)
        {
            IsDbExist();
            Log.Debug().Message("根据试验文件名称获取试验文件路径").Write();
            var sqlWhere = new StringBuilder("");
            names.ForEach(id => sqlWhere.Append("'").Append(id).Append("',"));
            var sql = string.Format("SELECT FilePath FROM TestIndex WHERE FileName IN ({0})",
                sqlWhere.Remove(sqlWhere.Length - 1, 1));
            var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
            con.Open();
            var files = new List<string>();
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    var reader = SqliteHelper.ExecuteReader(cmd, sql, null);
                    while (reader.Read())
                    {
                        files.Add(reader[0].ToString());
                    }

                    reader.Close();
                }
                catch (Exception e)
                {
                    Log.Error().Exception(e).Message("根据试验文件名称获取试验文件路径出错：{0}", e, sql).Write();
                }
            }

            return files;
        }

        /// <summary>
        /// 保存试验索引
        /// </summary>
        /// <param name="index"></param>
        /// <param name="selfQuery"></param>
        /// <returns></returns>
        public static int SaveIndex(TestIndex index, params FullData[] selfQuery)
        {
            IsDbExist();
            Log.Debug().Message("保存试验索引:{0}", index.TestId).Write();
            var sql =
                @"INSERT INTO TestIndex (
                    TestName,SampleName,SampleMaterial,SampleNO,SampleBatch,Tag,Remark,
                    TestType,OPName,TestId,StartTime,EndTime,FilePath,FileName,SysTime,Flag,ESignal{0}) 
                    VALUES (
                    @TestName,@SampleName,@SampleMaterial,@SampleNO,@SampleBatch,@Tag,@Remark,
                    @TestType,@OPName,@TestId,@StartTime,@EndTime,@FilePath,@FileName,@SysTime,@Flag,@ESignal{1})";
            var objs = new List<object> {
                index.TestName, index.SampleName, index.SampleMaterial, index.SampleNo, index.SampleBatch,index.Tag ?? "",
                index.Remark ?? "",index.TestType, index.OpName, index.TestId, index.StartTime, index.EndTime, index.FilePath,
                index.FileName, DateTime.Now, 0, index.ESignal
            };

            if (selfQuery.Length > 0)
            {
                var sb = new StringBuilder();
                var pm = new StringBuilder();
                foreach (var fd in selfQuery)
                {
                    sb.Append(",").Append(fd.ColumnName);
                    pm.Append(",@").Append(fd.ColumnName);
                    objs.Add(fd.Value);
                }

                sql = string.Format(sql, sb, pm);
            }
            else
            {
                sql = string.Format(sql, "", "");
            }
            try
            {
                return SqliteHelper.ExecuteNonQuery(DbFile, sql, objs.ToArray());
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("保存{0}试验索引失败:{1}",  index.TestId, sql).Write();
                return -1;
            }
        }

        /// <summary>
        /// 根据文件获取电子签名信息
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static List<Esignal> GetEsignals(string file)
        {
            Log.Debug().Message("获取{0}的电子签名", file).Write();
            var signs = new List<Esignal>();
            if (!File.Exists(file)) return signs;
            var sql = "SELECT COUNT(1) FROM sqlite_master WHERE type='table' AND name='ESignal'";
            var c = SqliteHelper.ExecuteScalar(file, sql, null).ToInt32();
            if (c == 0)
            {
                //创建电子签名表
                try
                {
                    SqliteHelper.ExecuteNonQuery(file,
                    @"CREATE TABLE [ESignal] ([SignalType] INT,[UserName] VARCHAR(200), [Date] DateTime,
                    [Remark] TEXT, [TestId] INT,[DeviceId] VARCHAR(50))", null);
                }
                catch (Exception e)
                {
                    Log.Error().Exception(e).Message("创建{0}的电子签名表失败",  file).Write();
                }

                return signs;
            }

            sql = "SELECT * FROM ESignal ORDER BY SignalType DESC";
            var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(file));
            con.Open();
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    var reader = SqliteHelper.ExecuteReader(cmd, sql, null);
                    while (reader.Read())
                    {
                        signs.Add(new Esignal
                        {
                            SignalType = reader["SignalType"].ToInt32(),
                            Date = reader["Date"].ToDateTime(),
                            DeviceId = reader["DeviceId"].ToString(),
                            Remark = reader["Remark"].ToString(),
                            TestId = reader["TestId"].ToInt32(),
                            UserName = reader["UserName"].ToString()
                        });
                    }

                    reader.Close();
                }
                catch (Exception e)
                {
                    Log.Error().Exception(e).Message("查询{0}的电子签名失败",  file).Write();
                    return signs;
                }
            }

            return signs;
        }

        /// <summary>
        /// 添加电子签名
        /// </summary>
        /// <param name="signal"></param>
        /// <returns></returns>
        public static int AddEsignals(Esignal signal)
        {
            Log.Debug().Message("为{0}添加一条电子签名", signal.FilePath).Write();
            if (!HasSignal(signal.FilePath))
            {
                using (var cnn = new SQLiteConnection(SqliteHelper.CreateConnectionString(signal.FilePath)))
                {
                    cnn.Open();
                    var tran = cnn.BeginTransaction();
                    try
                    {
                        SqliteHelper.ExecuteNonQuery(tran,
                        @"CREATE TABLE [ESignal] ([SignalType] INT,[UserName] VARCHAR(200), [Date] DateTime,
                        [Remark] TEXT, [TestId] INT,[DeviceId] VARCHAR(50))",
                       null);
                        tran.Commit();
                        //return 1;
                    }
                    catch (Exception e)
                    {
                        Log.Error().Exception(e).Message("为{0}创建电子签名表失败", signal.FilePath).Write();
                        tran.Rollback();
                        return -1;
                    }

                }
            }

            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(signal.FilePath)))
            {
                con.Open();
                var tran = con.BeginTransaction();
                var sql =
                    @"INSERT INTO ESignal ([SignalType],[UserName], [Date],[Remark], [TestId],[DeviceId]) 
                    VALUES (@SignalType,@UserName,@Date,@Remark,@TestId,@DeviceId)";

                try
                {
                    SqliteHelper.ExecuteNonQuery(tran, sql,
                    new object[] { signal.SignalType, signal.UserName, signal.Date, signal.Remark, signal.TestId,
                        string.Format("{0}{1}{2}{3}", Type, Device, Year, Sn) });
                    SqliteHelper.ExecuteNonQuery(DbFile,
                        "UPDATE TestIndex SET ESignal=@ESignal WHERE TestId=@TestId ",
                        new object[] { signal.SignalType, signal.TestId });
                    tran.Commit();
                    return 1;
                }
                catch (Exception e)
                {
                    Log.Error().Exception(e).Message("为{0}添加一条电子签名失败",  signal.FilePath).Write();
                    return -1;
                }
            }
        }


        private static bool HasSignal(string file)
        {
            var c = SqliteHelper.ExecuteScalar(file,
                "SELECT COUNT(*) FROM sqlite_master where type='table' and name='ESignal'");
            return Convert.ToInt32(c) > 0;
        }

        /// <summary>
        /// 修改电子签名备注
        /// </summary>
        /// <param name="sign"></param>
        /// <returns></returns>
        public static int UpdateEsignal(Esignal sign)
        {
            Log.Debug().Message("修改{0}的电子签名", sign.TestId).Write();
            try
            {
                return SqliteHelper.ExecuteNonQuery(sign.FilePath,
                    "UPDATE ESignal SET Remark=@Remark WHERE SignalType=@SignalType",
                    new object[] { sign.Remark, sign.SignalType });
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("修改{0}的电子签名失败",  sign.TestId).Write();
                return -1;
            }
        }

        /// <summary>
        /// 删除一条电子签名
        /// </summary>
        /// <param name="signal"></param>
        /// <returns></returns>
        public static int DeleteEsignals(Esignal signal)
        {
            Log.Debug().Message("为{0}删除一条电子签名", signal.FilePath).Write();
            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(signal.FilePath)))
            {
                con.Open();
                var tran = con.BeginTransaction();

                try
                {
                    SqliteHelper.ExecuteNonQuery(tran, "DELETE FROM ESignal WHERE SignalType=@SignalType",
                    new object[] { signal.SignalType });
                    SqliteHelper.ExecuteNonQuery(DbFile,
                        "UPDATE TestIndex SET ESignal=@ESignal WHERE TestId=@TestId ",
                        new object[] { signal.SignalType - 1, signal.TestId });
                    tran.Commit();
                    return 1;
                }
                catch (Exception e)
                {
                    Log.Error().Exception(e).Message("为{0}删除一条电子签名失败",  signal.FilePath).Write();
                    return -1;
                }
            }
        }

        /// <summary>
        /// 删除试验索引
        /// </summary>
        /// <param name="testid"></param>
        /// <param name="user"></param>
        /// <returns></returns>
        public static int DeleteIndex(int testid, string user)
        {
            var sql = "SELECT FilePath, OpName FROM TestIndex WHERE TestId=@TestId";
            var dt = SqliteHelper.ExecuteDataSet(DbFile, sql, new object[] { testid }).Tables[0];
            if (dt.Rows.Count == 0) return -1;
            var file = dt.Rows[0][0].ToString();
            if (!File.Exists(file)) return -1;
            if (IsGmp && !user.Equals(dt.Rows[0][1].ToString())) return -2;
            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile)))
            {
                con.Open();
                var tran = con.BeginTransaction();
                try
                {
                    var r = SqliteHelper.ExecuteNonQuery(tran,
                        "UPDATE FROM  TestIndex  SET Flag=-1 WHERE TestId=@TestId", new object[] { testid });
                    File.Delete(file);
                    tran.Commit();
                    return r;
                }
                catch (Exception e)
                {
                    tran.Rollback();
                    Log.Error().Exception(e).Message("删除TestId为{0}的试验记录失败",  testid).Write();
                    return -1;
                }
            }
        }

        /// <summary>
        /// 批量删除试验记录
        /// </summary>
        /// <param name="testids"></param>
        /// <returns></returns>
        public static int DeleteIndexs(List<int> testids)
        {
            if (IsGmp) return -2;
            var sqlWhere = new StringBuilder();
            foreach (var id in testids)
            {
                sqlWhere.Append(id).Append(",");
            }
            var sqlw = sqlWhere.Remove(sqlWhere.Length - 1, 1).ToString();
            var sql = string.Format("SELECT FilePath FROM TestIndex WHERE TestId in ({0})", sqlw);
            var dt = SqliteHelper.ExecuteDataSet(DbFile, sql, null).Tables[0];
            if (dt.Rows.Count == 0) return -1;
            SqliteHelper.ExecuteNonQuery(DbFile, string.Format(
                "UPDATE TestIndex SET Flag=-1 WHERE TestId IN ({0})", sqlw), null);
            var r = 0;
            foreach (DataRow dr in dt.Rows)
            {
                if (File.Exists(dr[0].ToString()))
                {
                    try
                    {
                        File.Delete(dr[0].ToString());
                        r++;
                    }
                    catch (Exception e)
                    {
                        Log.Debug().Message("删除文件{0}失败", e, dr[0]).Write();
                    }
                }
            }
            return r;
        }

        /// <summary>
        /// 更新试验记录的标签和备注
        /// </summary>
        /// <param name="tr"></param>
        /// <param name="user"></param>
        /// <returns></returns>
        public static int UpdateTagAndRemark(TagAndRemark tr, string user)
        {
            if (tr.TestId < 0) return -1;

            var opname = SqliteHelper.ExecuteScalar(DbFile, "SELECT OpName FROM TestIndex WHERE TestId=@TestId",
                new object[] { tr.TestId }).ToString();

            if (opname != user) return -403;

            var sql = new StringBuilder("UPDATE TestIndex SET Tag=@Tag,Remark=@Remark");
            var os = new List<object>();
            os.Add(tr.Tag);
            os.Add(tr.Remark);
            sql.Append(" WHERE TestId=@TestId");
            os.Add(tr.TestId);

            try
            {
                return SqliteHelper.ExecuteNonQuery(DbFile, sql.ToString(), os.ToArray());
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("更新试验编号{0}的标签或备注失败",  tr.TestId).Write();
                return -1;
            }
        }

        public static TagAndRemark GetTagAndRemark(int testId)
        {
            try
            {
                var tr = new TagAndRemark { TestId = testId };
                var dt = SqliteHelper.ExecuteDataSet(DbFile,
                    "SELECT Tag,Remark FROM TestIndex WHERE TestId=@TestId",
               new object[] { testId }).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    tr.Tag = dt.Rows[0][0].ToString();
                    tr.Remark = dt.Rows[0][1].ToString();
                }

                return tr;
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("获取试验编号{0}的标签或备注失败",  testId).Write();
                return null;
            }
        }

        #endregion

        #region 报警信息

        public static List<Alarm> GetAlarms()
        {
            IsDbExist();
            Log.Debug().Message("获取未处理的报警信息").Write();
            var sql = "SELECT * FROM Alarm ORDER BY Id DESC";
            var als = new List<Alarm>();
            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile)))
            {
                con.Open();
                var cmd = con.CreateCommand();
                var reader = SqliteHelper.ExecuteReader(cmd, sql, null);
                while (reader.Read())
                {
                    als.Add(new Alarm
                    {
                        Id = reader["Id"].ToInt32(),
                        AId = reader["AId"].ToInt32(),
                        AlarmInfo = reader["AlarmInfo"].ToString(),
                        AlarmStatus = reader["AlarmStatus"].ToInt32(),
                        AlarmType = reader["AlarmType"].ToInt32(),
                        CDate = reader["CDate"].ToDateTime()
                    });
                }
                reader.Close();
            }
            return als;
        }

        #endregion

        #endregion

        #region private

        /// <summary>
        /// 获取要删除的数据索引，并把状态标记为已删除
        /// </summary>
        /// <returns></returns>
        private static List<string> GetIndex(DateTime start, DateTime end, bool flag)
        {
            IsDbExist();
            var sql = "SELECT FilePath FROM TestIndex Where Flag=0 AND SysTime>=@startTime AND SysTime<=@endTime ";
            Log.Debug().Message("获取时间段{0}-{1}的试验文件路径", start, end).Write();
            var files = new List<string>();
            var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
            con.Open();
            var cmd = con.CreateCommand();
            try
            {
                var reader = SqliteHelper.ExecuteReader(cmd, sql, new object[] { start, end });
                while (reader.Read())
                {
                    files.Add(reader[0].ToString());
                }
                reader.Close();
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("获取时间段{0}-{1}的试验文件路径出错",  start, end).Write();
                return files;
            }

            if (!files.Any()) return files;

            if (!flag) return files;

            Log.Debug().Message("更新时间段{0}-{1}的试验文件状态", start, end).Write();
            sql = "UPDATE TestIndex SET Flag=-1 Where Flag=0 AND SysTime>=@start AND SysTime<=@end ";
            try
            {
                SqliteHelper.ExecuteNonQuery(DbFile, sql, new object[] { start, end });
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("更新时间段{0}-{1}的试验文件状态出错", start, end).Write();
            }
            return files;
        }

        /// <summary>
        ///  获取可用容量
        /// </summary>
        /// <param name="driveName"></param>
        /// <returns></returns>
        private static long GetDiskCapacity(string driveName)
        {
            foreach (DriveInfo drive in DriveInfo.GetDrives())
            {
                if (drive.IsReady && drive.Name == driveName)
                {
                    return drive.TotalFreeSpace;
                }
            }
            return -1;
        }

        /// <summary>
        /// 还原单个数据文件
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        private static void RestoreOne(string file)
        {
            var fn = Path.GetFileName(file);
            //Log.Debug().Message("还原数据文件{0}", fn);
            var o = SqliteHelper.ExecuteScalar(DbFile, "SELECT FilePath FROM TestIndex WHERE FileName=@FileName",
                new object[] { fn });
            if (o == null)
            {
                Log.Debug().Message("没有找到{0}对应的文件地址", fn).Write();
                return;
            }
            var ff = o.ToString();
            if (string.IsNullOrEmpty(ff))
            {
                Log.Debug().Message("没有找到{0}对应的文件地址", fn).Write();
                return;
            }
            var dir = Path.GetDirectoryName(ff);
            if (!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }
            try
            {
                File.Copy(file, ff, true);
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("还原文件{0}出错", fn).Write();
                return;
            }
            try
            {
                SqliteHelper.ExecuteNonQuery(DbFile, "UPDATE TestIndex SET Flag=0 WHERE FileName=@FileName",
                    new object[] { fn });
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("更新文件{0}的状态出错",  fn).Write();
            }
        }

        /// <summary>
        /// 是否包含电子签名表
        /// </summary>
        /// <param name="filePath">文件全路径</param>
        /// <returns></returns>
        private static bool HasSign(string filePath)
        {
            var c = SqliteHelper.ExecuteScalar(filePath,
                "SELECT COUNT(*) FROM sqlite_master where type='table' and name='Esignal'");
            return Convert.ToInt32(c) > 0;
        }

        /// <summary>
        /// 创建电子签名表
        /// </summary>
        /// <param name="filePath"></param>
        private static int CreateSign(string filePath)
        {
            if (File.Exists(filePath))
            {
                Log.Error().Message("试验文件{0}不存在", filePath).Write();
                return -1;
            }

            Log.Debug().Message("创建{0}的电子签名表", filePath).Write();
            const string sql =
                @"CREATE TABLE [Esignal] ([SignalType] INT,[UserName] VARCHAR(100), [Date] DateTime,[Remark] TEXT, 
                [TestId] INT,[DeviceId] VARCHAR(50))";
            try
            {
                return SqliteHelper.ExecuteNonQuery(filePath, sql, null);
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("创建{0}的电子签名表失败:{1}",  filePath, sql).Write();
                return -1;
            }
        }

        /// <summary>
        /// 根据文件名称获取试验信息
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        private static TestIndex GetIndex(string fileName)
        {
            IsDbExist();
            Log.Debug().Message("获取{0}的试验信息", fileName).Write();
            var sql = "SELECT * FROM TestIndex WHERE FileName=@FileName";
            var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
            var ti = new TestIndex();
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    var reader = SqliteHelper.ExecuteReader(cmd, sql, new object[] { fileName });
                    while (reader.Read())
                    {
                        ti.Id = reader["Id"].ToInt32();
                        ti.TestName = reader["TestName"].ToString();
                        ti.SampleName = reader["SampleName"].ToString();
                        ti.SampleMaterial = reader["SampleMaterial"].ToString();
                        ti.SampleNo = reader["SampleNO"].ToString();
                        ti.SampleBatch = reader["SampleBatch"].ToString();
                        ti.TestType = reader["TestType"].ToInt32();
                        ti.TestTypeName = reader["TestTypeName"].ToString();
                        ti.OpName = reader["OPName"].ToString();
                        ti.FilePath = reader["FilePath"].ToString();
                        ti.FileName = reader["FileName"].ToString();
                        ti.SysTime = reader["SysTime"].ToDateTime();
                        ti.StartTime = reader["StartTime"].ToDateTime();
                        ti.EndTime = reader["EndTime"].ToDateTime();
                        ti.TestId = reader["TestId"].ToInt32();
                        ti.ESignal = reader["ESignal"].ToInt32();
                    }
                    reader.Close();
                }
                catch (Exception e)
                {
                    Log.Error().Message("获取{0}的试验信息出错", e, fileName).Write();
                }
            }

            return ti;
        }

        /// <summary>
        /// 根据Id获取试验信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        private static TestIndex GetIndex(int id)
        {
            IsDbExist();
            Log.Debug().Message("获取{0}的试验信息", id).Write();
            var sql = "SELECT * FROM TestIndex WHERE Id=@Id";
            var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(DbFile));
            var ti = new TestIndex();
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    var reader = SqliteHelper.ExecuteReader(cmd, sql, new object[] { id });
                    while (reader.Read())
                    {
                        ti.Id = reader["Id"].ToInt32();
                        ti.TestName = reader["TestName"].ToString();
                        ti.SampleName = reader["SampleName"].ToString();
                        ti.SampleMaterial = reader["SampleMaterial"].ToString();
                        ti.SampleNo = reader["SampleNO"].ToString();
                        ti.SampleBatch = reader["SampleBatch"].ToString();
                        ti.TestType = reader["TestType"].ToInt32();
                        ti.TestTypeName = reader["TestTypeName"].ToString();
                        ti.OpName = reader["OPName"].ToString();
                        ti.FilePath = reader["FilePath"].ToString();
                        ti.FileName = reader["FileName"].ToString();
                        ti.SysTime = reader["SysTime"].ToDateTime();
                        ti.StartTime = reader["StartTime"].ToDateTime();
                        ti.EndTime = reader["EndTime"].ToDateTime();
                        ti.TestId = reader["TestId"].ToInt32();
                        ti.ESignal = reader["ESignal"].ToInt32();
                    }
                    reader.Close();
                }
                catch (Exception e)
                {
                    Log.Error().Message("获取{0}的试验信息出错", e, id).Write();
                }
            }

            return ti;
        }

        /// <summary>
        /// 更新签署状态
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="fileName"></param>
        /// <param name="sign"></param>
        /// <returns></returns>
        private static int UpdateSign(string filePath, string fileName, int sign)
        {
            IsDbExist();
            Log.Debug().Message("更新{0}的签署状态为{1}", fileName, sign).Write();
            var sql = "UPDATE TestIndex SET Esignal=@Esignal WHERE FileName=@FileName ";
            try
            {
                return SqliteHelper.ExecuteNonQuery(filePath, sql, new object[] { sign, fileName });
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("更新{0}的签署状态为{1}失败：{2}",  fileName, sign, sql).Write();
                return -1;
            }
        }

        /// <summary>
        /// 更新签署状态
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="id"></param>
        /// <param name="sign"></param>
        /// <returns></returns>
        private static int UpdateSign(string filePath, int id, int sign)
        {
            IsDbExist();
            Log.Debug().Message("更新{0}的签署状态为{1}", id, sign).Write();
            var sql = "UPDATE TestIndex SET Esignal=@Esignal WHERE Id=@Id ";
            try
            {
                return SqliteHelper.ExecuteNonQuery(filePath, sql, new object[] { sign, id });
            }
            catch (Exception e)
            {
                Log.Error().Exception(e).Message("更新{0}的签署状态为{1}失败：{2}",  id, sign, sql).Write();
                return -1;
            }
        }

        #endregion
    }
}
